﻿/* 
 * Since 2008 - 2012
 */

package com.hh.bss.project.dao.impl;

import static com.hh.bss.util.ObjectUtils.isEmpty;
import static com.hh.bss.util.ObjectUtils.isNotEmpty;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Repository;

import com.hh.bss.auth.util.AuthSqlUtil;
import com.hh.bss.base.BaseHibernateDao;
import com.hh.bss.common.MapObjectRowMapper;
import com.hh.bss.common.page.Page;
import com.hh.bss.project.dao.SearchProjectDao;
import com.hh.bss.project.entity.SearchProject;
import com.hh.bss.project.vo.query.ContactRecordQuery;
import com.hh.bss.project.vo.query.SearchProjectQuery;
import com.hh.bss.project.vo.query.SearchRpeportQuery;
import com.hh.bss.util.DateHelper;
import com.hh.bss.util.ExcelHelper;
import com.hh.bss.util.StringHelper;

@Repository
public class SearchProjectDaoImpl extends BaseHibernateDao<SearchProject, java.lang.Integer> implements SearchProjectDao<SearchProject, java.lang.Integer> {

	public Class getEntityClass() {
		return SearchProject.class;
	}

	public Page findPage(SearchProjectQuery query) {
		// XsqlBuilder syntax,please see
		// http://code.google.com/p/rapid-xsqlbuilder
		// [column]为字符串拼接, {column}为使用占位符.
		// [column]为使用字符串拼接,如username='[username]',偷懒时可以使用字符串拼接
		// [column] 为PageRequest的属性

		Class[] os = { SearchProjectQuery.class };
		Class property = org.springframework.beans.BeanUtils.findPropertyType(query.getField_type(), os);
		if (property != null) {
			if ("java.lang.String".equals(property.getName())) {

				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), query.getQuery());

			} else if ("java.lang.Integer".equals(property.getName())) {

				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), StringHelper.parseInteger(query.getQuery(), 0));

			} else if ("java.lang.Double".equals(property.getName())) {
				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), StringHelper.parseDouble(query.getQuery(), 0));
			} else if ("java.util.Date".equals(property.getName())) {
				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), DateHelper.toDate(query.getQuery(), DateHelper.DEFAULT_DATE_FORMAT));
			}
		}
		// 生成sql2的原因是为了不喜欢使用xsqlbuilder的同学，请修改生成器模板，删除本段的生成
		StringBuilder sql2 = new StringBuilder("select t from SearchProject t where 1=1 ");
		if (isNotEmpty(query.getId())) {
			sql2.append(" and  t.id = :id ");
		}
		if (isNotEmpty(query.getName())) {
			sql2.append(" and  t.name like :name ");
			query.setName("%" + query.getName() + "%");

		}
		if (isNotEmpty(query.getAccount())) {
			sql2.append(" and  t.account like :account ");
			query.setAccount("%" + query.getAccount() + "%");

		}
		if (isNotEmpty(query.getPartner())) {
			sql2.append(" and  t.partner like :partner ");
			query.setPartner("%" + query.getPartner() + "%");

		}
		if (isNotEmpty(query.getIndustrys())) {
			sql2.append(" and  t.industrys like :industrys ");
			query.setIndustrys("%" + query.getIndustrys() + "%");

		}
		if (isNotEmpty(query.getFunctions())) {
			sql2.append(" and  t.functions like :functions ");
			query.setFunctions("%" + query.getFunctions() + "%");

		}
		if (isNotEmpty(query.getLevels())) {
			sql2.append(" and  t.levels like :levels ");
			query.setLevels("%" + query.getLevels() + "%");

		}
		if (isNotEmpty(query.getCities())) {
			sql2.append(" and  t.cities like :cities ");
			query.setLevels("%" + query.getCities() + "%");
		}
		if (isNotEmpty(query.getCreator())) {
			sql2.append(" and  t.creator like :creator ");
			query.setCreator("%" + query.getCreator() + "%");

		}
		if (isNotEmpty(query.getCreateDtBegin())) {
			sql2.append(" and  t.createDt >= :createDtBegin ");
		}
		if (isNotEmpty(query.getCreateDtEnd())) {
			sql2.append(" and  t.createDt <= :createDtEnd ");
		}
		if (isNotEmpty(query.getModifier())) {
			sql2.append(" and  t.modifier like :modifier ");
			query.setModifier("%" + query.getModifier() + "%");

		}
		if (isNotEmpty(query.getModifiedDtBegin())) {
			sql2.append(" and  t.modifiedDt >= :modifiedDtBegin ");
		}
		if (isNotEmpty(query.getModifiedDtEnd())) {
			sql2.append(" and  t.modifiedDt <= :modifiedDtEnd ");
		}
		if (isNotEmpty(query.getSortColumns())) {
			sql2.append(" order by :sortColumns ");
		}

		return pageQuery(sql2.toString(), query);
	}

	public Page findProjectPage(SearchProjectQuery query) {
		StringBuffer sql = new StringBuffer();
		List values = new ArrayList();
		// select a.id,a.name,a.industrys,a.functions,a.levels,a.cities,b.cc
		// companyCount,c.cc talentCount,d.cc cvCount,e.cc
		// recomCount,a.create_dt createDt,a.creator from search_project a
		// left join (select project_id,count(*) cc from search_project_company
		// group by project_id) b on a.id=b.project_id
		// left join (select project_id,count(*) cc from search_project_cv group
		// by project_id) c on a.id=c.project_id
		// left join (
		// select project_id,count(*) cc from search_project_cv b0
		// where exists (select 1 from cv_trainning b1 where b0.cv_id=b1.cv_id)
		// or exists (select 1 from cv_certificate b2 where b0.cv_id=b2.cv_id)
		// or (select 1 from cv_education b3 where b0.cv_id=b3.cv_id)
		// group by project_id ) d on a.id=d.project_id
		// left join (select a1.project_id,count(*) cc from
		// contract_job_cv_recom a0,contract_job_search_project a1
		// where a0.job_id=a1.job_id group by a1.project_id) e on
		// a.id=e.project_id
		sql.append(" select * from ( ");
		sql.append(" 	select a.id,a.account,a.partner,a.name,a.industrys,a.functions,a.levels,a.cities,ifnull(b.cc,0) companyCount,ifnull(c.cvCount,0) talentCount,ifnull(c.cvlvCount,0) cvCount,ifnull(e.cc,0) recomCount,a.create_dt createDt,a.creator,a.modified_dt modifiedDt, a.modifier,a.start_d startD,datediff(curdate(),a.create_dt) days from search_project a ");
		sql.append(" 	 left join  (select project_id,count(*) cc from search_project_company  group by project_id) b on a.id=b.project_id ");
		// sql.append(" 	 left join (select project_id,count(*) cc from search_project_cv  group by project_id) c on a.id=c.project_id ");
		sql.append(" 	 left join ( ");
		/*
		 * sql.append(" 	select project_id,count(*) cc from search_project_cv b0 "
		 * ); sql.append(
		 * " 	where exists (select 1 from cv_trainning b1 where b0.cv_id=b1.cv_id) "
		 * ); sql.append(
		 * " 	or exists (select 1 from cv_certificate b2 where b0.cv_id=b2.cv_id) "
		 * ); sql.append(
		 * " 	or exists (select 1 from cv_education b3 where b0.cv_id=b3.cv_id) "
		 * ); sql.append(
		 * " 	or exists (select 1 from cv_work b4 where b0.cv_id=b4.cv_id) ");
		 * sql.append(
		 * " 	or exists (select 1 from cv_attachment b5 where b0.cv_id=b5.cv_id) "
		 * ); sql.append(" 	group by project_id ) d on a.id=d.project_id ");
		 */
		sql.append(" 	select project_id,count(distinct b1.id) cvCount,sum(if(b1.cv_status='F',1,0)) cvlvCount from search_project_cv b0,cv b1 where b0.cv_id=b1.id ");
		sql.append(" 	group by project_id ) c on a.id=c.project_id ");
		sql.append(" 	 left join (select a1.project_id,count(*) cc from  contract_job_cv_recom a0,contract_job_search_project a1 ");
		sql.append(" 	where a0.job_id=a1.job_id group by a1.project_id) e on a.id=e.project_id ");
		sql.append(" ) aa where 1=1 ");

		if (isNotEmpty(query.getId())) {
			sql.append(" and aa.id = ? ");
			values.add(query.getId());
		}
		if (isNotEmpty(query.getName())) {
			sql.append(" and aa.name like ? ");
			values.add("%" + query.getName() + "%");
		}

		if (query.isBoth()) {
			sql.append(" and  ( aa.account = ? or find_in_set(?,aa.partner)>0  )  ");
			values.add(query.getOnlineUser().getUsername());
			values.add(query.getOnlineUser().getUsername());
		}
		if (isNotEmpty(query.getAccount())) {
			/*
			 * if(isNotEmpty(query.getPartner())){
			 * sql.append(" and  ( aa.account = ? "); }else{
			 * sql.append(" and   aa.account = ? "); }
			 */
			sql.append(" and   aa.account = ? ");
			values.add(query.getAccount());

			// values.add("%"+query.getAccount() + "%");
		}
		if (isNotEmpty(query.getIndustrysParent())) {
			if (isEmpty(query.getIndustrys())) {
				sql.append(" and  exists( select 1 from system_dic a where aa.industrys = a.id  and a.category='INDUSTRY' and a.parent_id in ( " + query.getIndustrysParent() + ")) ");
				// sql.append(" and industrys in (select a11.id  from  system_dic a11 where a11.category='INDUSTRY' and a11.parent_id =").append(query.getIndustrysParent()).append(")) ");
			}
		}
		if (isNotEmpty(query.getPartner())) {
			/*
			 * if(isNotEmpty(query.getAccount())){
			 * sql.append(" or (find_in_set(?,aa.partner)>0 )) "); }else{
			 * sql.append(" or (find_in_set(?,aa.partner)>0 ) "); }
			 */
			// sql.append(" or ( aa.partner in (?)  ");
			sql.append(" and  (find_in_set(?,aa.partner)>0 ) ");
			values.add(query.getPartner());
		}
		if (isNotEmpty(query.getIndustrys())) {
			sql.append(" and (find_in_set(?,aa.industrys))");
			values.add(query.getIndustrys());
		}
		if (isNotEmpty(query.getFunctionsParent())) {
			if (isEmpty(query.getFunctions())) {
				sql.append(" and  exists( select 1 from system_dic a where aa.functions = a.id  and a.category='JOB' and a.parent_id in ( " + query.getFunctionsParent() + ")) ");
				// sql.append(" and  aa.functions in (select a22.id  from  system_dic a22 where a22.category='JOB' and a22.parent_id =").append(query.getFunctionsParent()).append(")) ");
				// values.add(query.getFunctionsParent());
			}
		}
		if (isNotEmpty(query.getFunctions())) {
			sql.append(" and (find_in_set(?,aa.functions)) ");
			values.add(query.getFunctions());
		}
		if (isNotEmpty(query.getLevels())) {
			sql.append(" and aa.levels = ? ");
			values.add(query.getLevels());
		}
		if (isNotEmpty(query.getCities())) {
			sql.append(" and aa.cities = ? ");
			values.add(query.getCities());
			// values.add();
		}
		if (isNotEmpty(query.getStartDtBegin()) && isEmpty(query.getStartDtEnd())) {
			sql.append(" and  aa.startD  >= ? ");
			values.add(query.getStartDtBegin());
		} else if (isEmpty(query.getStartDtBegin()) && isNotEmpty(query.getStartDtEnd())) {
			sql.append(" and  aa.startD  <= ? ");
			values.add(query.getStartDtEnd());
		} else if (isNotEmpty(query.getStartDtBegin()) && isNotEmpty(query.getStartDtEnd())) {
			sql.append(" and ( aa.startD between ? and  ? ) ");
			values.add(query.getStartDtBegin());
			values.add(query.getStartDtEnd());
		}
		if (isNotEmpty(query.getCreateDtBegin()) && isEmpty(query.getCreateDtEnd())) {
			sql.append(" and  (aa.createDt  >= ? )");
			values.add(query.getCreateDtBegin());
		} else if (isEmpty(query.getCreateDtBegin()) && isNotEmpty(query.getCreateDtEnd())) {
			sql.append(" and  aa.createDt  <= ? ");
			values.add(query.getCreateDtEnd());
		} else if (isNotEmpty(query.getStartDtBegin()) && isNotEmpty(query.getCreateDtEnd())) {
			sql.append(" and ( aa.createDt between ? and  ? ) ");
			values.add(query.getCreateDtBegin());
			values.add(query.getCreateDtEnd());
		}
		if (isNotEmpty(query.getCreateDt())) {
			sql.append(" and  (aa.createDt  >= ? or aa.startD is not null )");
			values.add(query.getCreateDt());
		}

		if (isNotEmpty(query.getSortColumns())) {
			sql.append(" order by aa.").append(query.getSortColumns());
			// sql.append(" order by aa.? ");
			// values.add("aa."+ query.getSortColumns());
			// values.add(query.getDir());
		} else {
			// sql.append(" order by aa.modifiedDt desc ");
			sql.append(" order by ifnull(aa.startD,aa.createDt) desc ");
		}
		return super.getJdbcDao().createPage(sql.toString(), values.toArray(), new MapObjectRowMapper(), query.getPageSize(), query.getPageNumber());
	}

	public Page findContactRecordPage(ContactRecordQuery query) {
		// TODO Auto-generated method stub
		StringBuffer sql = new StringBuffer();
		List values = new ArrayList();
		sql.append(" select * from ( ");
		sql.append(" select concat('cc',a.id) id, a.id targetId,a.ref_cvid cvId,'CC' crType,a.creator,a.create_dt createDt,a.modifier,a.modified_dt modifiedDt,a.comment from search_project_cc_list a where a.ref_cvid=?");
		sql.append("  union all ");
		sql.append(" select concat('tr',b.id) id, b.id targetId,b.cv_id cvId,'CR' crType,b.creator,b.create_dt createDt,b.modifier,b.modified_dt modifiedDt,b.comment from search_project_cv_crm b where b.cv_id=?");
		sql.append("  union all ");
		sql.append(" select concat('gi',c.id) id, c.id targetId,c.cv_id cvId,'GI' crType,c.creator,c.create_dt createDt,c.modifier,c.modified_dt modifiedDt,c.content comment from search_project_interview c where c.cv_id=?");
		sql.append("  union all ");
		sql.append(" select concat('ki',d.id) id, d.id targetId,d.cv_id cvId,'KI' crType,d.creator,d.create_dt createDt,d.modifier,d.modified_dt modifiedDt,d.comment from (select aa.id,aa.creator,aa.create_dt,aa.modifier,aa.modified_dt,bb.cv_id,bb.job_id,aa.content comment from  contract_job_cv_interview aa,contract_job_cv_recom bb where aa.recom_id=bb.id ");
		// 增加权限控制
		if (query.getOnlineUser() != null) {
			String authsql = AuthSqlUtil.authJobSQL(query.getOnlineUser().getUsername(), "bb.job_id", "aa.creator");
			if (!StringHelper.isEmpty(authsql))
				sql.append(authsql);
		}
		sql.append(" ) d where d.cv_id=?");
		sql.append(" ) t where 1=1 ");

		values.add(query.getCvId());
		values.add(query.getCvId());
		values.add(query.getCvId());
		values.add(query.getCvId());

		if (isNotEmpty(query.getBeginDt())) {
			sql.append(" and t.createDt >= ? ");
			values.add(query.getBeginDt());
		}
		if (isNotEmpty(query.getEndDt())) {
			sql.append(" and t.createDt < ? ");
			values.add(DateHelper.add(query.getEndDt(), DateHelper.DAY, 1));
		}
		if (isNotEmpty(query.getCrType())) {
			sql.append(" and t.crType = ? ");
			values.add(query.getCrType());
		}

		sql.append(" order by t.createDt desc ");

		return super.getJdbcDao().createPage(sql.toString(), values.toArray(), new MapObjectRowMapper(), query.getPageSize(), query.getPageNumber());

	}

	@Override
	public void exportSearchRpeport(SearchRpeportQuery query, HttpServletResponse response) {
		Workbook wb = new XSSFWorkbook();
		Sheet sheet1 = wb.createSheet("Search Report");
		Sheet sheet2 = wb.createSheet("顾问面试人选明细");
		Sheet sheet3 = wb.createSheet("联系人选明细");
		List<String> titleList = new ArrayList<String>();
		titleList.add("客户");
		titleList.add("职位");
		titleList.add("目标公司数");
		titleList.add("行业数量");
		titleList.add("行业明细");
		titleList.add("共寻访人选数量");
		titleList.add("面试人选数量");
		titleList.add("感兴趣并发送职位描述");
		titleList.add("占比");
		titleList.add("本科及以上");
		titleList.add("占比");
		titleList.add("英语流利");
		titleList.add("占比");
		// 创建表头
		Row row = sheet1.createRow(0);
		Cell cell = null;
		// 填充单元格
		for (int i = 0; i < titleList.size(); i++) {
			cell = row.createCell(i);
			cell.setCellValue(titleList.get(i));
		}
		// 表头设置完毕

		// 行二
		row = sheet1.createRow(1);

		StringBuffer sql = new StringBuffer();
		// 判断导出公司 还是 SL
		// if (isNotEmpty(query.getCompanyId())) {
		List values = new ArrayList();
		// 统计各项数据(客户 职位 目标公司数量)
		sql.append("select * from  ( ");
		sql.append(" select a.abbr_name abbrName,c.jobNames,e.projectIds from company a ");
		sql.append(" inner join customer b on a.id = b.company_id ");
		sql.append(" left join (select a.cust_id,group_concat(a.name) jobnames  from contract_job a group by a.cust_id  ) c on b.id = c.cust_id ");
		// sql.append(" left join (select count(distinct a1.company_id) companyTotal,c1.cust_id   from search_project_company a1 left join contract_job_search_project b1 on a1.project_id = b1.project_id left join contract_job  c1 on b1.job_id = c1.id group by c1.cust_id ) d on b.id = d.cust_id ");
		sql.append(" left join (select c1.cust_id,group_concat(distinct convert(b1.project_id,char)) projectIds  from   contract_job_search_project b1  left join contract_job  c1 on b1.job_id = c1.id group by c1.cust_id ) e on b.id = e.cust_id ");
		sql.append(" where a.id = ? ");
		sql.append(" ) t where 1=1 ");
		if (isNotEmpty(query.getCompanyId())) {
			values.add(query.getCompanyId());
		} else {
			values.add(0);
		}
		String projectIds = query.getProjectIds(); // Note:查询和填入的 projectIds
		List list = this.getJdbcDao().findByQuery(sql.toString(), values.toArray(), new MapObjectRowMapper());

		if (list != null && list.size() > 0) {
			Map map = (HashMap) list.get(0);
			projectIds = (String) map.get("projectIds");
			// 设置数据
			cell = row.createCell(0); // 客户
			cell.setCellValue((String) map.get("abbrName"));
			cell = row.createCell(1); // 职位
			cell.setCellValue((String) map.get("jobNames"));

		}
		cell = row.createCell(2); // 涉及目标公司数
		cell.setCellValue(this.getTargetCompanyTotal(projectIds));

		List industryList = this.getSLIndustryDetail(projectIds);

		if (industryList != null && industryList.size() > 0) {
			Map m = (HashMap) industryList.get(0);
			cell = row.createCell(3); // 涉及行业数
			cell.setCellValue((Long) m.get("industryTotal"));
			cell = row.createCell(4); // 行业明细
			cell.setCellValue((String) m.get("industryNames"));
		}

		cell = row.createCell(5); // 寻访总数
		long total = this.getSearchCvTotal(projectIds);
		cell.setCellValue(total);
		cell = row.createCell(6); // 面试数
		long interview = this.getInterviewCvTotal(projectIds);
		cell.setCellValue(interview);
		cell = row.createCell(7); // JD数
		long jdTotal = this.getJDCvTotal(projectIds);
		cell.setCellValue(jdTotal);
		cell = row.createCell(8); // 占比
		cell.setCellValue((double) jdTotal / total);
		cell = row.createCell(9); // 本科
		long degreeTotal = this.getDegreeCvTotal(projectIds);
		cell.setCellValue(degreeTotal);
		cell = row.createCell(10); // 占比
		cell.setCellValue((double) degreeTotal / total);
		cell = row.createCell(11); // 英语
		long enTotal = this.getEnglishCvTotal(projectIds);
		cell.setCellValue(enTotal);
		cell = row.createCell(12); // 占比
		cell.setCellValue((double) enTotal / total);

		// 面试记录
		// Map<String, String> title = new HashMap<String, String>();
		// 表头
		// SL 人选编号 人选姓名 现任公司 现职位 面试日期 面试官 面试评价
		List<String> title = new ArrayList<String>();
		title.add("所在SL");
		title.add("人选编号");
		title.add("姓名");
		title.add("现任公司");
		title.add("现职位");
		title.add("面试日期");
		title.add("面试官");
		title.add("面试评价");

		List<String> key = new ArrayList<String>();
		key.add("name");
		key.add("id");
		key.add("cv_name");
		key.add("company");
		key.add("position");
		key.add("iv_dt");
		key.add("interviewer");
		key.add("content");


		ExcelHelper.setExcelSheetData(sheet2, getInterviewRecord(query, projectIds), title, key);
		title = new ArrayList<String>();
		title.add("所在SL");
		title.add("人选编号");
		title.add("姓名");
		title.add("现任公司");
		title.add("现职位");
		title.add("年龄");
		title.add("工作年限");
		title.add("外语能力");
		title.add("年薪范围");
		title.add("教育背景(学历)");
		title.add("面试日期");
		title.add("联系记录");

		key = new ArrayList<String>();

		key.add("name");
		key.add("id");
		key.add("cv_name");
		key.add("company");
		key.add("position");
		key.add("age");
		key.add("workyear");
		key.add("cv_fl_level");
		key.add("current_salary");
		key.add("degree");
		key.add("crm_dt");
		key.add("comment");

	
		ExcelHelper.setExcelSheetData(sheet3, getContactRecord(query, projectIds), title, key);

		// 设置完毕 开始下载
		try {
			String titles = new String("SearchReport.xlsx");
			response.setCharacterEncoding("utf-8");
			response.setHeader("Content-Disposition", "attachment;filename=\"" + titles + "\"");
			response.setContentType("application/vnd.ms-excel");
			ServletOutputStream out = response.getOutputStream();
			wb.write(out);
			out.flush();
			out.close();
			wb = null;
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	/**
	 * 行业数量 和 和 行业明细
	 * 
	 * @param projectIds
	 *            SL
	 * @return
	 */
	public List getSLIndustryDetail(String projectIds) {
		StringBuffer sql = new StringBuffer();
		List values = new ArrayList();
		sql.append("select * from  ( ");
		sql.append("select  count(distinct b.id ) industryTotal ,group_concat(distinct b.code_name_cn) industryNames from search_project a,system_dic b  where find_in_set(b.id,industrys) and find_in_set(a.id,?)");
		sql.append(" ) t where 1=1 ");
		values.add(projectIds);
		return this.getJdbcDao().findByQuery(sql.toString(), values.toArray(), new MapObjectRowMapper());

	}

	public Long getSearchCvTotal(String projectIds) {
		Long cvTotal = 0L; // 寻访总数
		StringBuffer sql = new StringBuffer();
		List values = new ArrayList();
		sql.append("select * from  ( ");
		sql.append(" select count(distinct a.cv_id  ) cvTotal  from search_project_cv a where find_in_set(a.project_id,?) and  exists (select 1 from cv a1 where a1.is_delete = 0 and a1.id = a.cv_id ) ");
		sql.append(" ) t where 1=1 ");
		values.add(projectIds);
		List l = this.getJdbcDao().findByQuery(sql.toString(), values.toArray(), new MapObjectRowMapper());
		if (l != null && l.size() > 0) {
			Map map = (Map) l.get(0);
			cvTotal = (Long) map.get("cvTotal");
		}
		return cvTotal;
	}

	/**
	 * 面试 人选数量
	 * 
	 * @param projectIds
	 * @return
	 */
	public Long getInterviewCvTotal(String projectIds) {
		Long interviewTotal = 0L; // 寻访总数
		StringBuffer sql = new StringBuffer();
		List values = new ArrayList();
		sql.append("select * from  ( ");
		sql.append(" select count( distinct a.cv_id  ) interviewTotal  from search_project_cv a,search_project_interview b  where a.cv_id = b.cv_id   and  find_in_set(a.project_id,?) and  exists (select 1 from cv a1 where a1.is_delete = 0 and a1.id = a.cv_id ) ");
		sql.append(" ) t where 1=1 ");
		values.add(projectIds);

		List l = this.getJdbcDao().findByQuery(sql.toString(), values.toArray(), new MapObjectRowMapper());
		if (l != null && l.size() > 0) {
			Map map = (Map) l.get(0);
			interviewTotal = (Long) map.get("interviewTotal");
		}
		return interviewTotal;
	}

	/**
	 * 发JD人选数
	 * 
	 * @param projectIds
	 * @return
	 */
	public Long getJDCvTotal(String projectIds) {
		Long jdTotal = 0L; // 寻访总数
		StringBuffer sql = new StringBuffer();
		List values = new ArrayList();
		sql.append("select * from  ( ");
		sql.append(" select count( distinct a.cv_id  ) jdTotal  from search_project_cv a,contract_job_cv_jd  b  where a.cv_id = b.cv_id  and b.type = 1   and  find_in_set(a.project_id,?) and  exists (select 1 from cv a1 where a1.is_delete = 0 and a1.id = a.cv_id  )  ");
		sql.append(" ) t where 1=1 ");
		values.add(projectIds);
		List l = this.getJdbcDao().findByQuery(sql.toString(), values.toArray(), new MapObjectRowMapper());
		if (l != null && l.size() > 0) {
			Map map = (Map) l.get(0);
			jdTotal = (Long) map.get("jdTotal");
		}
		return jdTotal;
	}

	public Long getDegreeCvTotal(String projectIds) {
		Long degreeTotal = 0L; // 寻访总数
		StringBuffer sql = new StringBuffer();
		List values = new ArrayList();
		sql.append("select * from  ( ");
		sql.append(" select count( distinct a.id ) degreeTotal  from cv a inner join search_project_cv b on a.id = b.cv_id  where a.is_delete = 0 and a.degree between 2042 and 2045 and   find_in_set(b.project_id,?) ");
		sql.append(" ) t where 1=1 ");
		values.add(projectIds);
		List l = this.getJdbcDao().findByQuery(sql.toString(), values.toArray(), new MapObjectRowMapper());
		if (l != null && l.size() > 0) {
			Map map = (Map) l.get(0);
			degreeTotal = (Long) map.get("degreeTotal");
		}
		return degreeTotal;
	}

	/**
	 * 英语流利
	 * 
	 * @param projectIds
	 * @return
	 */
	public Long getEnglishCvTotal(String projectIds) {
		Long englishTotal = 0L; // 寻访总数
		StringBuffer sql = new StringBuffer();
		List values = new ArrayList();
		sql.append("select * from  ( ");
		sql.append(" select count( distinct a.id ) englishTotal  from cv a inner join search_project_cv b on a.id = b.cv_id  where a.is_delete = 0 and find_in_set(a.cv_fl_level,'30,40') and   find_in_set(b.project_id,?) ");
		sql.append(" ) t where 1=1 ");
		values.add(projectIds);
		List l = this.getJdbcDao().findByQuery(sql.toString(), values.toArray(), new MapObjectRowMapper());
		if (l != null && l.size() > 0) {
			Map map = (Map) l.get(0);
			englishTotal = (Long) map.get("englishTotal");
		}
		return englishTotal;
	}

	public Long getTargetCompanyTotal(String projectIds) {
		Long companyTotal = 0L; // 寻访总数
		StringBuffer sql = new StringBuffer();
		List values = new ArrayList();
		sql.append("select * from  ( ");
		sql.append(" select count(distinct a.company_id ) companyTotal  from search_project_company a where find_in_set(a.project_id,?) ");
		sql.append(" ) t where 1=1 ");
		values.add(projectIds);
		List l = this.getJdbcDao().findByQuery(sql.toString(), values.toArray(), new MapObjectRowMapper());
		if (l != null && l.size() > 0) {
			Map map = (Map) l.get(0);
			companyTotal = (Long) map.get("companyTotal");
		}
		return companyTotal;
	}

	/**
	 * 顾问面试记录
	 * 
	 * @return
	 */
	public List getInterviewRecord(SearchRpeportQuery query, String projectIds) {
		StringBuffer sql = new StringBuffer();
		List values = new ArrayList();
		Map m = new HashMap();
		sql.append("select * from  ( ");
		sql.append("select c.name,b.id,b.cv_name,b.company,b.position,a.iv_dt,interviewer,a.content from  search_project_interview a ");
		sql.append(" inner join cv b on a.cv_id = b.id ");
		sql.append(" inner join search_project c on a.project_id = c.id ");
		sql.append(" where b.is_delete = 0  and find_in_set(a.project_id,?)");
		sql.append(" ) t where 1=1 ");
		values.add(projectIds);
		if (isNotEmpty(query.getDateStart())) {
			sql.append(" and t.iv_dt > ? ");
			values.add(query.getDateStart());
		}
		if (isNotEmpty(query.getDateEnd())) {
			sql.append(" and t.iv_dt < ? ");
			values.add(DateHelper.add(query.getDateEnd(), DateHelper.DAY, 1));
		}
		return this.getJdbcDao().findByQuery(sql.toString(), values.toArray(), new MapObjectRowMapper());

	}

	/**
	 * 联系记录(不包括顾问面试人选)
	 * 
	 * @return
	 */
	public List getContactRecord(SearchRpeportQuery query, String projectIds) {
		StringBuffer sql = new StringBuffer();
		List values = new ArrayList();
		sql.append("select * from  ( ");
		sql.append(" select c.name,b.id,b.cv_name,b.company,b.position, ");
		sql.append(" (year(curdate())-year(b.birthday))-(right(curdate(),5)<right(b.birthday,5)) as age ,");
		sql.append("(year(curdate())-year(b.work_year))-(right(curdate(),5)<right(b.work_year,5)) as workyear ,");
		sql.append(" case ");
		sql.append(" when b.cv_fl_level = 10 then '入门' ");
		sql.append(" when b.cv_fl_level = 20 then '一般' ");
		sql.append(" when b.cv_fl_level = 30 then '高级' ");
		sql.append(" when b.cv_fl_level = 40 then '专家' ");
		sql.append(" end cv_fl_level , e.code_name_cn current_salary,f.code_name_cn degree,a.crm_dt,a.comment ");
		sql.append(" from  search_project_cv_crm a  ");
		sql.append(" inner join cv b on a.cv_id = b.id ");
		sql.append(" inner join search_project c on a.project_id = c.id ");
		sql.append(" left join  cv_salary d on b.id = d.cv_id  ");
		sql.append(" left join system_dic e on d.current_salary = e.id  ");
		sql.append(" left join system_dic f on b.degree = f.id ");
		sql.append(" where b.is_delete = 0 and not exists (select 1 from search_project_interview a1 where a.cv_id = a1.cv_id and a.project_id = a1.project_id ) ");
		sql.append(" and find_in_set(a.project_id,?) ");
		sql.append(" ) t where 1=1 ");
		values.add(projectIds);
		if (isNotEmpty(query.getDateStart())) {
			sql.append(" and t.crm_dt > ? ");
			values.add(query.getDateStart());
		}
		if (isNotEmpty(query.getDateEnd())) {
			sql.append(" and t.crm_dt < ? ");
			values.add(DateHelper.add(query.getDateEnd(), DateHelper.DAY, 1));
		}
		return this.getJdbcDao().findByQuery(sql.toString(), values.toArray(), new MapObjectRowMapper());

	}

}
